{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# LAB 3b:  BigQuery ML Model Linear Feature Engineering/Transform.\n",
    "\n",
    "**Learning Objectives**\n",
    "\n",
    "1. Create and evaluate linear model with BigQuery's ML.FEATURE_CROSS\n",
    "1. Create and evaluate linear model with BigQuery's ML.FEATURE_CROSS and ML.BUCKETIZE\n",
    "1. Create and evaluate linear model with ML.TRANSFORM\n",
    "\n",
    "\n",
    "## Introduction \n",
    "In this notebook, we will create multiple linear models to predict the weight of a baby before it is born, using increasing levels of feature engineering using BigQuery ML. If you need a refresher, you can go back and look how we made a baseline model in the previous notebook [BQML Baseline Model](../solutions/3a_bqml_baseline_babyweight.ipynb).\n",
    "\n",
    "We will create and evaluate a linear model using BigQuery's ML.FEATURE_CROSS, create and evaluate a linear model using BigQuery's ML.FEATURE_CROSS and ML.BUCKETIZE, and create and evaluate a linear model using BigQuery's ML.TRANSFORM.\n",
    "\n",
    "Each learning objective will correspond to a __#TODO__ in this student lab notebook -- try to complete this notebook first and then review the [solution notebook](../solutions/3b_bqml_linear_transform_babyweight.ipynb)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "hJ7ByvoXzpVI"
   },
   "source": [
    "## Load necessary libraries"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "mC9K9Dpx1ztf"
   },
   "source": [
    "Check that the Google BigQuery library is installed and if not, install it. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 609
    },
    "colab_type": "code",
    "id": "RZUQtASG10xO",
    "outputId": "5612d6b0-9730-476a-a28f-8fdc14f4ecde"
   },
   "outputs": [],
   "source": [
    "%%bash\n",
    "pip freeze | grep google-cloud-bigquery==1.6.1 || \\\n",
    "pip install google-cloud-bigquery==1.6.1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "clnaaqQsXkwC"
   },
   "source": [
    "## Verify tables exist\n",
    "\n",
    "Run the following cells to verify that we previously created the dataset and data tables. If not, go back to lab [1b_prepare_data_babyweight](../solutions/1b_prepare_data_babyweight.ipynb) to create them."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery\n",
    "-- LIMIT 0 is a free query; this allows us to check that the table exists.\n",
    "SELECT * FROM babyweight.babyweight_data_train\n",
    "LIMIT 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery\n",
    "-- LIMIT 0 is a free query; this allows us to check that the table exists.\n",
    "SELECT * FROM babyweight.babyweight_data_eval\n",
    "LIMIT 0"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "FbSRbuJ-fYtK"
   },
   "source": [
    "## Lab Task #1: Model 1:  Apply the ML.FEATURE_CROSS clause to categorical features\n",
    "\n",
    "BigQuery ML now has ML.FEATURE_CROSS, a pre-processing clause that performs a feature cross with syntax ML.FEATURE_CROSS(STRUCT(features), degree) where features are comma-separated categorical columns and degree is highest degree of all combinations."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Create model with feature cross."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "cellView": "both",
    "colab": {},
    "colab_type": "code",
    "id": "Z3U2FxVklrlU"
   },
   "outputs": [],
   "source": [
    "%%bigquery\n",
    "CREATE OR REPLACE MODEL\n",
    "    babyweight.model_1\n",
    "\n",
    "OPTIONS (\n",
    "    MODEL_TYPE=\"LINEAR_REG\",\n",
    "    INPUT_LABEL_COLS=[\"weight_pounds\"],\n",
    "    L2_REG=0.1,\n",
    "    DATA_SPLIT_METHOD=\"NO_SPLIT\") AS\n",
    "\n",
    "SELECT\n",
    "    # TODO: Add base features and label\n",
    "    ML.FEATURE_CROSS(\n",
    "        # TODO: Cross categorical features\n",
    "    ) AS gender_plurality_cross\n",
    "FROM\n",
    "    babyweight.babyweight_data_train"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "G6tpoYhcIgs4"
   },
   "source": [
    "#### Create two SQL statements to evaluate the model."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "cellView": "both",
    "colab": {},
    "colab_type": "code",
    "id": "t10fGqSfIgtA"
   },
   "outputs": [],
   "source": [
    "%%bigquery\n",
    "SELECT\n",
    "    *\n",
    "FROM\n",
    "    ML.EVALUATE(MODEL babyweight.model_1,\n",
    "    (\n",
    "    SELECT\n",
    "        # TODO: Add same features and label as training\n",
    "    FROM\n",
    "        babyweight.babyweight_data_eval\n",
    "    ))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "cellView": "both",
    "colab": {},
    "colab_type": "code",
    "id": "uC-gyvAmIgtE"
   },
   "outputs": [],
   "source": [
    "%%bigquery\n",
    "SELECT\n",
    "    # TODO: Select just the calculated RMSE\n",
    "FROM\n",
    "    ML.EVALUATE(MODEL babyweight.model_1,\n",
    "    (\n",
    "    SELECT\n",
    "        # TODO: Add same features and label as training\n",
    "    FROM\n",
    "        babyweight.babyweight_data_eval\n",
    "    ))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Lab Task #2: Model 2:  Apply the BUCKETIZE Function \n",
    " \n",
    "Bucketize is a pre-processing function that creates \"buckets\" (e.g bins) - e.g. it bucketizes a continuous numerical feature into a string feature with bucket names as the value with syntax ML.BUCKETIZE(feature, split_points) with split_points being an array of numerical points to determine bucket bounds."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Apply the BUCKETIZE function within FEATURE_CROSS.\n",
    "* Hint:  Create a model_2."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery\n",
    "CREATE OR REPLACE MODEL\n",
    "    babyweight.model_2\n",
    "\n",
    "OPTIONS (\n",
    "    MODEL_TYPE=\"LINEAR_REG\",\n",
    "    INPUT_LABEL_COLS=[\"weight_pounds\"],\n",
    "    L2_REG=0.1,\n",
    "    DATA_SPLIT_METHOD=\"NO_SPLIT\") AS\n",
    "\n",
    "SELECT\n",
    "    weight_pounds,\n",
    "    is_male,\n",
    "    mother_age,\n",
    "    plurality,\n",
    "    gestation_weeks,\n",
    "    ML.FEATURE_CROSS(\n",
    "        STRUCT(\n",
    "            is_male,\n",
    "            ML.BUCKETIZE(\n",
    "                # TODO: Bucketize mother_age\n",
    "            ) AS bucketed_mothers_age,\n",
    "            plurality,\n",
    "            ML.BUCKETIZE(\n",
    "                # TODO: Bucketize gestation_weeks\n",
    "            ) AS bucketed_gestation_weeks\n",
    "        )\n",
    "    ) AS crossed\n",
    "FROM\n",
    "    babyweight.babyweight_data_train"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "AVPXGKZ374v7"
   },
   "source": [
    "#### Create three SQL statements to EVALUATE the model.\n",
    "\n",
    "Let's now retrieve the training statistics and evaluate the model."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery\n",
    "SELECT * FROM ML.TRAINING_INFO(MODEL babyweight.model_2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We now evaluate our model on our eval dataset:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery\n",
    "SELECT\n",
    "    *\n",
    "FROM\n",
    "    ML.EVALUATE(MODEL babyweight.model_2,\n",
    "    (\n",
    "    SELECT\n",
    "        # TODO: Add same features and label as training\n",
    "    FROM\n",
    "        babyweight.babyweight_data_eval))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's select the `mean_squared_error` from the evaluation table we just computed and square it to obtain the rmse."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery\n",
    "SELECT\n",
    "    SQRT(mean_squared_error) AS rmse\n",
    "FROM\n",
    "    ML.EVALUATE(MODEL babyweight.model_2,\n",
    "    (\n",
    "    SELECT\n",
    "        # TODO: Add same features and label as training\n",
    "    FROM\n",
    "        babyweight.babyweight_data_eval))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Lab Task #3: Model 3:  Apply the TRANSFORM clause\n",
    "\n",
    "Before we perform our prediction, we should encapsulate the entire feature set in a TRANSFORM clause. This way we can have the same transformations applied for training and prediction without modifying the queries."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's apply the TRANSFORM clause to the model_3 and run the query."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery\n",
    "CREATE OR REPLACE MODEL\n",
    "    babyweight.model_3\n",
    "\n",
    "TRANSFORM(\n",
    "    # TODO: Add base features and label as you would in select\n",
    "    # TODO: Add transformed features as you would in select\n",
    ")\n",
    "\n",
    "OPTIONS (\n",
    "    MODEL_TYPE=\"LINEAR_REG\",\n",
    "    INPUT_LABEL_COLS=[\"weight_pounds\"],\n",
    "    L2_REG=0.1,\n",
    "    DATA_SPLIT_METHOD=\"NO_SPLIT\") AS\n",
    "\n",
    "SELECT\n",
    "    *\n",
    "FROM\n",
    "    babyweight.babyweight_data_train"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's retrieve the training statistics:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery\n",
    "SELECT * FROM ML.TRAINING_INFO(MODEL babyweight.model_3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We now evaluate our model on our eval dataset:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery\n",
    "SELECT\n",
    "    *\n",
    "FROM\n",
    "    ML.EVALUATE(MODEL babyweight.model_3,\n",
    "    (\n",
    "    SELECT\n",
    "        *\n",
    "    FROM\n",
    "        babyweight.babyweight_data_eval\n",
    "    ))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's select the `mean_squared_error` from the evaluation table we just computed and square it to obtain the rmse."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery\n",
    "SELECT\n",
    "    SQRT(mean_squared_error) AS rmse\n",
    "FROM\n",
    "    ML.EVALUATE(MODEL babyweight.model_3,\n",
    "    (\n",
    "    SELECT\n",
    "        *\n",
    "    FROM\n",
    "        babyweight.babyweight_data_eval\n",
    "    ))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Lab Summary: \n",
    "In this lab, we created and evaluated a linear model using BigQuery's ML.FEATURE_CROSS, created and evaluated a linear model using BigQuery's ML.FEATURE_CROSS and ML.BUCKETIZE, and created and evaluated a linear model using BigQuery's ML.TRANSFORM and L2 regularization."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Copyright 2019 Google Inc. Licensed under the Apache License, Version 2.0 (the \"License\"); you may not use this file except in compliance with the License. You may obtain a copy of the License at http://www.apache.org/licenses/LICENSE-2.0 Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an \"AS IS\" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "colab": {
   "collapsed_sections": [],
   "name": "10.3.2019-DRAFT-_1 - FeatEnG - LABS.ipynb",
   "provenance": [],
   "toc_visible": true
  },
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.5.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
